from app import app, get_db_connection
from flask import render_template, request, redirect, url_for, flash, session
from functools import wraps
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime

# Login verification decorator
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

# Admin verification decorator
def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session or session.get('role') != 'admin':
            flash('Admin privileges required')
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

@app.route('/')
def index():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    # Get approved activities
    cursor.execute("""
        SELECT * FROM activities 
        WHERE status = 'approved' 
        ORDER BY created_at DESC 
        LIMIT 6
    """)
    activities = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template('index.html', activities=activities)

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        email = request.form['email']
        password = request.form['password']
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.execute('SELECT * FROM users WHERE email = %s AND role != "admin" AND status = "active"', (email,))
        user = cursor.fetchone()
        cursor.close()
        conn.close()
        
        if not user:
            flash('Account does not exist or has been suspended')
            return redirect(url_for('login'))
            
        if check_password_hash(user['password'], password):
            session['user_id'] = user['id']
            session['role'] = user['role']
            return redirect(url_for('dashboard'))
        flash('Incorrect password')
    return render_template('login.html')

@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        name = request.form['name']
        email = request.form['email']
        password = request.form['password']
        role = request.form['role']
        
        # Generate password hash using pbkdf2:sha256 method
        password_hash = generate_password_hash(password, method='pbkdf2:sha256')
        
        conn = get_db_connection()
        cursor = conn.cursor()
        try:
            cursor.execute(
                'INSERT INTO users (name, email, password, role, status) VALUES (%s, %s, %s, %s, %s)',
                (name, email, password_hash, role, 'active')
            )
            conn.commit()
            flash('Registration successful, please login')
            return redirect(url_for('login'))
        except:
            flash('Registration failed, email may already be registered')
        finally:
            cursor.close()
            conn.close()
    return render_template('register.html')

@app.route('/dashboard')
@login_required
def dashboard():
    role = session.get('role')
    if role == 'admin':
        return redirect(url_for('admin_dashboard'))
    elif role == 'volunteer':
        return redirect(url_for('volunteer_dashboard'))
    else:
        return redirect(url_for('organization_dashboard'))

@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('index'))

@app.route('/admin/dashboard')
@admin_required
def admin_dashboard():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Get statistics
    cursor.execute('SELECT COUNT(*) as total_users FROM users')
    total_users = cursor.fetchone()['total_users']
    
    cursor.execute('SELECT COUNT(*) as pending_users FROM users WHERE status = "pending"')
    pending_users = cursor.fetchone()['pending_users']
    
    cursor.execute('SELECT COUNT(*) as total_activities FROM activities')
    total_activities = cursor.fetchone()['total_activities']
    
    cursor.execute('SELECT COUNT(*) as pending_activities FROM activities WHERE status = "pending"')
    pending_activities = cursor.fetchone()['pending_activities']
    
    # Get recently registered users
    cursor.execute("""
        SELECT * FROM users 
        WHERE role != 'admin'
        ORDER BY created_at DESC 
        LIMIT 10
    """)
    recent_users = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    stats = {
        'total_users': total_users,
        'pending_users': pending_users,
        'total_activities': total_activities,
        'pending_activities': pending_activities
    }
    
    return render_template('admin/dashboard.html', stats=stats, recent_users=recent_users)

@app.route('/admin/users')
@admin_required
def admin_users():
    page = request.args.get('page', 1, type=int)
    per_page = 10
    role = request.args.get('role')
    status = request.args.get('status')
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Build query conditions
    conditions = ['role != "admin"']
    params = []
    
    if role:
        conditions.append('role = %s')
        params.append(role)
    if status:
        conditions.append('status = %s')
        params.append(status)
    
    where_clause = ' AND '.join(conditions)
    
    # Get total record count
    count_query = f'SELECT COUNT(*) as count FROM users WHERE {where_clause}'
    cursor.execute(count_query, params)
    total = cursor.fetchone()['count']
    
    # Get paginated data
    query = f'''
        SELECT * FROM users 
        WHERE {where_clause}
        ORDER BY created_at DESC 
        LIMIT %s OFFSET %s
    '''
    cursor.execute(query, params + [per_page, (page - 1) * per_page])
    users = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('admin/users.html',
                         users=users,
                         page=page,
                         has_next=total > page * per_page)

@app.route('/admin/activities')
@admin_required
def admin_activities():
    page = request.args.get('page', 1, type=int)
    per_page = 10
    status = request.args.get('status')
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Build query conditions
    conditions = ['1=1']
    params = []
    
    if status:
        conditions.append('a.status = %s')
        params.append(status)
    
    where_clause = ' AND '.join(conditions)
    
    # Get total record count
    count_query = f'''
        SELECT COUNT(*) as count 
        FROM activities a 
        WHERE {where_clause}
    '''
    cursor.execute(count_query, params)
    total = cursor.fetchone()['count']
    
    # Get paginated data
    query = f'''
        SELECT a.*, u.name as organization_name
        FROM activities a
        LEFT JOIN users u ON a.organization_id = u.id
        WHERE {where_clause}
        ORDER BY a.created_at DESC
        LIMIT %s OFFSET %s
    '''
    cursor.execute(query, params + [per_page, (page - 1) * per_page])
    activities = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('admin/activities.html',
                         activities=activities,
                         page=page,
                         has_next=total > page * per_page)

@app.route('/admin/users/<int:user_id>/approve', methods=['POST'])
@admin_required
def admin_approve_user(user_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('UPDATE users SET status = "active" WHERE id = %s', (user_id,))
    conn.commit()
    cursor.close()
    conn.close()
    flash('User approved')
    return redirect(url_for('admin_users'))

@app.route('/admin/users/<int:user_id>/suspend', methods=['POST'])
@admin_required
def admin_suspend_user(user_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('UPDATE users SET status = "suspended" WHERE id = %s', (user_id,))
    conn.commit()
    cursor.close()
    conn.close()
    flash('User suspended')
    return redirect(url_for('admin_users'))

@app.route('/admin/users/<int:user_id>/activate', methods=['POST'])
@admin_required
def admin_activate_user(user_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('UPDATE users SET status = "active" WHERE id = %s', (user_id,))
    conn.commit()
    cursor.close()
    conn.close()
    flash('User activated')
    return redirect(url_for('admin_users'))

@app.route('/admin/activities/<int:activity_id>/approve', methods=['POST'])
@admin_required
def admin_approve_activity(activity_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('UPDATE activities SET status = "approved" WHERE id = %s', (activity_id,))
    conn.commit()
    cursor.close()
    conn.close()
    flash('Activity approved')
    return redirect(url_for('admin_activities'))

@app.route('/admin/activities/<int:activity_id>/reject', methods=['POST'])
@admin_required
def admin_reject_activity(activity_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('UPDATE activities SET status = "rejected" WHERE id = %s', (activity_id,))
    conn.commit()
    cursor.close()
    conn.close()
    flash('Activity rejected')
    return redirect(url_for('admin_activities'))

@app.route('/volunteer/dashboard')
@login_required
def volunteer_dashboard():
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Get user information
    cursor.execute('SELECT * FROM users WHERE id = %s', (session['user_id'],))
    user = cursor.fetchone()
    
    # Get statistics - 修改统计逻辑
    cursor.execute('''
        SELECT 
            COUNT(DISTINCT r.activity_id) as total_activities,
            SUM(CASE WHEN a.end_date < NOW() THEN TIMESTAMPDIFF(HOUR, a.start_date, a.end_date) ELSE 0 END) as total_hours,
            SUM(CASE WHEN a.start_date > NOW() AND r.status = 'approved' THEN 1 ELSE 0 END) as pending_activities
        FROM registrations r
        JOIN activities a ON r.activity_id = a.id
        WHERE r.volunteer_id = %s
    ''', (session['user_id'],))
    stats = cursor.fetchone()
    
    # Get upcoming activities
    cursor.execute('''
        SELECT a.*, u.name as organization_name, r.status as registration_status
        FROM activities a
        JOIN users u ON a.organization_id = u.id
        JOIN registrations r ON a.id = r.activity_id
        WHERE r.volunteer_id = %s 
        AND a.start_date > NOW()
        AND r.status = 'approved'
        ORDER BY a.start_date ASC
        LIMIT 5
    ''', (session['user_id'],))
    upcoming_activities = cursor.fetchall()
    
    # Get latest notifications
    cursor.execute('''
        SELECT * FROM notifications
        WHERE user_id = %s
        ORDER BY created_at DESC
        LIMIT 5
    ''', (session['user_id'],))
    notifications = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    # 处理空值情况
    if stats['total_activities'] is None:
        stats['total_activities'] = 0
    if stats['total_hours'] is None:
        stats['total_hours'] = 0
    if stats['pending_activities'] is None:
        stats['pending_activities'] = 0
    
    return render_template('volunteer/dashboard.html',
                         user=user,
                         stats=stats,
                         upcoming_activities=upcoming_activities,
                         notifications=notifications)

@app.route('/volunteer/activities')
@login_required
def volunteer_activities():
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    page = request.args.get('page', 1, type=int)
    per_page = 12
    keyword = request.args.get('keyword', '')
    category = request.args.get('category', '')
    date = request.args.get('date', '')
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Build query conditions
    conditions = ['a.status = "approved" AND a.end_date > NOW()']
    params = []
    
    if keyword:
        conditions.append('(a.title LIKE %s OR a.description LIKE %s)')
        params.extend(['%' + keyword + '%'] * 2)
    if category:
        conditions.append('a.category = %s')
        params.append(category)
    if date:
        conditions.append('DATE(a.start_date) = %s')
        params.append(date)
    
    where_clause = ' AND '.join(conditions)
    
    # Get total record count
    count_query = f'''
        SELECT COUNT(*) as count
        FROM activities a
        WHERE {where_clause}
    '''
    cursor.execute(count_query, params)
    total = cursor.fetchone()['count']
    
    # Get paginated data
    query = f'''
        SELECT a.*, u.name as organization_name,
               EXISTS(SELECT 1 FROM registrations r 
                     WHERE r.activity_id = a.id AND r.volunteer_id = %s) as is_registered
        FROM activities a
        JOIN users u ON a.organization_id = u.id
        WHERE {where_clause}
        ORDER BY a.start_date ASC
        LIMIT %s OFFSET %s
    '''
    cursor.execute(query, [session['user_id']] + params + [per_page, (page - 1) * per_page])
    activities = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('volunteer/activities.html',
                         activities=activities,
                         page=page,
                         has_next=total > page * per_page)

@app.route('/volunteer/my-activities')
@login_required
def volunteer_my_activities():
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    page = request.args.get('page', 1, type=int)
    per_page = 10
    status = request.args.get('status', '')
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Build query conditions
    conditions = ['r.volunteer_id = %s']
    params = [session['user_id']]
    
    if status:
        conditions.append('r.status = %s')
        params.append(status)
    
    where_clause = ' AND '.join(conditions)
    
    # Get total record count
    count_query = f'''
        SELECT COUNT(*) as count
        FROM registrations r
        WHERE {where_clause}
    '''
    cursor.execute(count_query, params)
    total = cursor.fetchone()['count']
    
    # Get paginated data
    query = f'''
        SELECT a.*, u.name as organization_name, r.status, r.feedback, r.feedback_time,
               NOW() > a.start_date as is_started
        FROM registrations r
        JOIN activities a ON r.activity_id = a.id
        JOIN users u ON a.organization_id = u.id
        WHERE {where_clause}
        ORDER BY a.start_date DESC
        LIMIT %s OFFSET %s
    '''
    cursor.execute(query, params + [per_page, (page - 1) * per_page])
    activities = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('volunteer/my_activities.html',
                         activities=activities,
                         page=page,
                         has_next=total > page * per_page)

@app.route('/volunteer/profile')
@login_required
def volunteer_profile():
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute('SELECT * FROM users WHERE id = %s', (session['user_id'],))
    user = cursor.fetchone()
    cursor.close()
    conn.close()
    
    return render_template('volunteer/profile.html', user=user)

@app.route('/volunteer/profile/update', methods=['POST'])
@login_required
def volunteer_profile_update():
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        # Update user information
        cursor.execute('''
            UPDATE users 
            SET name = %s, phone = %s, birth_date = %s, gender = %s,
                address = %s, skills = %s, introduction = %s
            WHERE id = %s
        ''', (
            request.form.get('name'),
            request.form.get('phone'),
            request.form.get('birth_date'),
            request.form.get('gender'),
            request.form.get('address'),
            request.form.get('skills'),
            request.form.get('introduction'),
            session['user_id']
        ))
        
        conn.commit()
        flash('Profile updated successfully')
    except Exception as e:
        flash('Failed to update profile')
    finally:
        cursor.close()
        conn.close()
    
    return redirect(url_for('volunteer_profile'))

@app.route('/volunteer/change-password', methods=['POST'])
@login_required
def volunteer_change_password():
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    old_password = request.form.get('old_password')
    new_password = request.form.get('new_password')
    confirm_password = request.form.get('confirm_password')
    
    if not all([old_password, new_password, confirm_password]):
        flash('Please fill in all password fields')
        return redirect(url_for('volunteer_profile'))
    
    if new_password != confirm_password:
        flash('New passwords do not match')
        return redirect(url_for('volunteer_profile'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    cursor.execute('SELECT password FROM users WHERE id = %s', (session['user_id'],))
    user = cursor.fetchone()
    if not check_password_hash(user['password'], old_password):
        flash('Current password is incorrect')
        return redirect(url_for('volunteer_profile'))
    
    # Update password
    password_hash = generate_password_hash(new_password, method='pbkdf2:sha256')
    cursor.execute('UPDATE users SET password = %s WHERE id = %s',
                  (password_hash, session['user_id']))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('Password updated successfully')
    return redirect(url_for('volunteer_profile'))

@app.route('/volunteer/activity/<int:activity_id>/register', methods=['POST'])
@login_required
def volunteer_register_activity(activity_id):
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Check if activity exists and can be registered
    cursor.execute('''
        SELECT * FROM activities 
        WHERE id = %s AND status = 'approved' 
        AND current_participants < max_participants
        AND end_date > NOW()
    ''', (activity_id,))
    activity = cursor.fetchone()
    
    if not activity:
        flash('Activity does not exist or registration is not available')
        return redirect(url_for('volunteer_activities'))
    
    # Check if already registered
    cursor.execute('''
        SELECT * FROM registrations
        WHERE activity_id = %s AND volunteer_id = %s
    ''', (activity_id, session['user_id']))
    if cursor.fetchone():
        flash('You have already registered for this activity')
        return redirect(url_for('volunteer_activities'))
    
    # Create registration record
    cursor.execute('''
        INSERT INTO registrations (activity_id, volunteer_id, status)
        VALUES (%s, %s, 'pending')
    ''', (activity_id, session['user_id']))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('Registration successful, pending approval')
    return redirect(url_for('volunteer_my_activities'))

@app.route('/volunteer/activity/<int:activity_id>/cancel', methods=['POST'])
@login_required
def volunteer_cancel_registration(activity_id):
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Check registration record
    cursor.execute('''
        SELECT r.*, a.start_date 
        FROM registrations r
        JOIN activities a ON r.activity_id = a.id
        WHERE r.activity_id = %s AND r.volunteer_id = %s
        AND r.status = 'approved' AND a.start_date > NOW()
    ''', (activity_id, session['user_id']))
    registration = cursor.fetchone()
    
    if not registration:
        flash('Cannot cancel registration')
        return redirect(url_for('volunteer_my_activities'))
    
    # Cancel registration
    cursor.execute('''
        UPDATE registrations 
        SET status = 'rejected'
        WHERE activity_id = %s AND volunteer_id = %s
    ''', (activity_id, session['user_id']))
    
    # Update activity participant count
    cursor.execute('''
        UPDATE activities 
        SET current_participants = current_participants - 1
        WHERE id = %s
    ''', (activity_id,))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('Registration cancelled')
    return redirect(url_for('volunteer_my_activities'))

@app.route('/volunteer/activity/<int:activity_id>/feedback', methods=['GET', 'POST'])
@login_required
def volunteer_feedback(activity_id):
    if session.get('role') != 'volunteer':
        flash('Access denied')
        return redirect(url_for('dashboard'))
    
    if request.method == 'POST':
        feedback = request.form.get('feedback')
        if not feedback:
            flash('Please provide feedback')
            return redirect(url_for('volunteer_feedback', activity_id=activity_id))
        
        conn = get_db_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            UPDATE registrations 
            SET feedback = %s, feedback_time = NOW()
            WHERE activity_id = %s AND volunteer_id = %s
            AND status = 'completed'
        ''', (feedback, activity_id, session['user_id']))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        flash('Feedback submitted')
        return redirect(url_for('volunteer_my_activities'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    cursor.execute('''
        SELECT a.*, r.feedback, r.feedback_time
        FROM activities a
        JOIN registrations r ON a.id = r.activity_id
        WHERE a.id = %s AND r.volunteer_id = %s
        AND r.status = 'completed'
    ''', (activity_id, session['user_id']))
    activity = cursor.fetchone()
    
    cursor.close()
    conn.close()
    
    if not activity:
        flash('Cannot submit feedback')
        return redirect(url_for('volunteer_my_activities'))
    
    return render_template('volunteer/feedback.html', activity=activity)

@app.route('/organization/dashboard')
@login_required
def organization_dashboard():
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    org_id = session.get('user_id')
    
    # Get statistics
    cursor.execute('''
        SELECT 
            COUNT(*) as total_activities,
            SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_activities,
            SUM(CASE WHEN status = 'approved' AND end_date > NOW() THEN 1 ELSE 0 END) as active_activities
        FROM activities 
        WHERE organization_id = %s
    ''', (org_id,))
    stats = cursor.fetchone()
    
    # Get total volunteer count for this organization
    cursor.execute('''
        SELECT COUNT(DISTINCT volunteer_id) as total_volunteers
        FROM registrations r
        JOIN activities a ON r.activity_id = a.id
        WHERE a.organization_id = %s
    ''', (org_id,))
    volunteer_stats = cursor.fetchone()
    stats['total_volunteers'] = volunteer_stats['total_volunteers']
    
    # Get recent activities
    cursor.execute('''
        SELECT 
            a.*,
            CASE 
                WHEN a.status = 'pending' THEN 'warning'
                WHEN a.status = 'approved' THEN 'success'
                WHEN a.status = 'rejected' THEN 'danger'
                WHEN a.status = 'completed' THEN 'info'
            END as status_class,
            CASE 
                WHEN a.status = 'pending' THEN 'Pending'
                WHEN a.status = 'approved' THEN 'Approved'
                WHEN a.status = 'rejected' THEN 'Rejected'
                WHEN a.status = 'completed' THEN 'Completed'
            END as status_text
        FROM activities a
        WHERE organization_id = %s
        ORDER BY created_at DESC
        LIMIT 10
    ''', (org_id,))
    recent_activities = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('organization/dashboard.html',
                         stats=stats,
                         recent_activities=recent_activities)

@app.route('/organization/activities')
@login_required
def organization_activities():
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    page = request.args.get('page', 1, type=int)
    per_page = 10
    status = request.args.get('status')
    category = request.args.get('category')
    search = request.args.get('search')
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    org_id = session.get('user_id')
    
    # Build query conditions
    conditions = ['organization_id = %s']
    params = [org_id]
    
    if status:
        conditions.append('status = %s')
        params.append(status)
    if category:
        conditions.append('category = %s')
        params.append(category)
    if search:
        conditions.append('title LIKE %s')
        params.append(f'%{search}%')
    
    where_clause = ' AND '.join(conditions)
    
    # Get total record count
    count_query = f'SELECT COUNT(*) as count FROM activities WHERE {where_clause}'
    cursor.execute(count_query, params)
    total = cursor.fetchone()['count']
    
    # Get paginated data
    query = f'''
        SELECT 
            a.*,
            CASE 
                WHEN status = 'pending' THEN 'warning'
                WHEN status = 'approved' THEN 'success'
                WHEN status = 'rejected' THEN 'danger'
                WHEN status = 'completed' THEN 'info'
            END as status_class,
            CASE 
                WHEN status = 'pending' THEN 'Pending'
                WHEN status = 'approved' THEN 'Approved'
                WHEN status = 'rejected' THEN 'Rejected'
                WHEN status = 'completed' THEN 'Completed'
            END as status_text,
            CASE 
                WHEN category = 'education' THEN 'Education'
                WHEN category = 'environment' THEN 'Environment'
                WHEN category = 'healthcare' THEN 'Healthcare'
                WHEN category = 'elderly' THEN 'Elderly Care'
                ELSE 'Other'
            END as category_text
        FROM activities a
        WHERE {where_clause}
        ORDER BY created_at DESC
        LIMIT %s OFFSET %s
    '''
    cursor.execute(query, params + [per_page, (page - 1) * per_page])
    activities = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('organization/activities.html',
                         activities=activities,
                         page=page,
                         has_next=total > page * per_page)

@app.route('/admin/login', methods=['GET', 'POST'])
def admin_login():
    if request.method == 'POST':
        email = request.form['email']
        password = request.form['password']
        
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        # Only query active admin accounts
        cursor.execute('SELECT * FROM users WHERE email = %s AND role = "admin" AND status = "active"', (email,))
        user = cursor.fetchone()
        cursor.close()
        conn.close()
        
        if not user:
            flash('Admin account does not exist or has been suspended')
            return redirect(url_for('admin_login'))
            
        if check_password_hash(user['password'], password):
            session['user_id'] = user['id']
            session['role'] = user['role']
            return redirect(url_for('admin_dashboard'))
        flash('Incorrect password')
    return render_template('admin/login.html')

@app.route('/organization/activity/new', methods=['GET', 'POST'])
def organization_new_activity():
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        title = request.form.get('title')
        description = request.form.get('description')
        category = request.form.get('category')
        location = request.form.get('location')
        start_date = request.form.get('start_date')
        end_date = request.form.get('end_date')
        max_participants = request.form.get('max_participants')
        
        if not all([title, description, category, location, start_date, end_date, max_participants]):
            flash('Please fill in all required fields')
            return redirect(url_for('organization_new_activity'))
        
        try:
            conn = get_db_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO activities (
                    title, description, category, location, 
                    start_date, end_date, max_participants,
                    organization_id, status, current_participants,
                    created_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            ''', (
                title, description, category, location,
                start_date, end_date, max_participants,
                session['user_id'], 'pending', 0
            ))
            
            conn.commit()
            cursor.close()
            conn.close()
            
            flash('Activity created successfully, pending admin approval')
            return redirect(url_for('organization_activities'))
            
        except Exception as e:
            flash('Failed to create activity, please try again later')
            return redirect(url_for('organization_new_activity'))
    
    return render_template('organization/new_activity.html')

@app.route('/organization/profile')
@login_required
def organization_profile():
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Get organization information
    cursor.execute('SELECT * FROM users WHERE id = %s', (session['user_id'],))
    organization = cursor.fetchone()
    
    cursor.close()
    conn.close()
    
    if not organization:
        flash('Organization not found')
        return redirect(url_for('index'))
    
    return render_template('organization/profile.html', organization=organization)

@app.route('/organization/profile/update', methods=['POST'])
@login_required
def organization_profile_update():
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    # Get form data
    name = request.form.get('name')
    phone = request.form.get('phone')
    address = request.form.get('address')
    introduction = request.form.get('introduction')
    
    if not name:
        flash('Organization name is required')
        return redirect(url_for('organization_profile'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        # Update organization information
        cursor.execute('''
            UPDATE users 
            SET name = %s, phone = %s, address = %s, introduction = %s
            WHERE id = %s
        ''', (name, phone, address, introduction, session['user_id']))
        
        conn.commit()
        flash('Organization profile updated')
    except Exception as e:
        flash('Failed to update profile. Please try again.')
    finally:
        cursor.close()
        conn.close()
    
    return redirect(url_for('organization_profile'))

@app.route('/organization/change-password', methods=['POST'])
@login_required
def organization_change_password():
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    old_password = request.form.get('old_password')
    new_password = request.form.get('new_password')
    confirm_password = request.form.get('confirm_password')
    
    if not all([old_password, new_password, confirm_password]):
        flash('Please fill in all password fields')
        return redirect(url_for('organization_profile'))
    
    if new_password != confirm_password:
        flash('New passwords do not match')
        return redirect(url_for('organization_profile'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Verify old password
    cursor.execute('SELECT password FROM users WHERE id = %s', (session['user_id'],))
    user = cursor.fetchone()
    if not check_password_hash(user['password'], old_password):
        flash('Current password is incorrect')
        return redirect(url_for('organization_profile'))
    
    # Update password
    password_hash = generate_password_hash(new_password, method='pbkdf2:sha256')
    cursor.execute('UPDATE users SET password = %s WHERE id = %s',
                  (password_hash, session['user_id']))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('Password updated successfully')
    return redirect(url_for('organization_profile'))

@app.route('/activity/<int:activity_id>')
def activity_detail(activity_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Get activity details
    cursor.execute('''
        SELECT a.*, u.name as organization_name, u.phone as organization_phone,
               u.address as organization_address, u.introduction as organization_introduction,
               u.email as organization_email
        FROM activities a
        JOIN users u ON a.organization_id = u.id
        WHERE a.id = %s AND a.status = 'approved'
    ''', (activity_id,))
    activity = cursor.fetchone()
    
    if not activity:
        flash('Activity does not exist or is not approved')
        return redirect(url_for('index'))
    
    # Get registered count
    cursor.execute('''
        SELECT COUNT(*) as registered_count
        FROM registrations
        WHERE activity_id = %s AND status = 'approved'
    ''', (activity_id,))
    registration_count = cursor.fetchone()['registered_count']
    
    # Check if current user is registered
    is_registered = False
    registration_status = None
    registration = None
    if 'user_id' in session:
        cursor.execute('''
            SELECT *
            FROM registrations
            WHERE activity_id = %s AND volunteer_id = %s
        ''', (activity_id, session['user_id']))
        registration = cursor.fetchone()
        if registration:
            is_registered = True
            registration_status = registration['status']
    
    cursor.close()
    conn.close()
    
    return render_template('activity_detail.html',
                         activity=activity,
                         registration_count=registration_count,
                         is_registered=is_registered,
                         registration_status=registration_status,
                         registration=registration)

@app.route('/activity/<int:activity_id>/register', methods=['POST'])
@login_required
def activity_register(activity_id):
    if session.get('role') != 'volunteer':
        flash('Only volunteers can register for activities')
        return redirect(url_for('activity_detail', activity_id=activity_id))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Check if activity exists and can be registered
    cursor.execute('''
        SELECT * FROM activities 
        WHERE id = %s AND status = 'approved' 
        AND current_participants < max_participants
        AND end_date > NOW()
    ''', (activity_id,))
    activity = cursor.fetchone()
    
    if not activity:
        flash('Activity does not exist or cannot be registered')
        return redirect(url_for('activity_detail', activity_id=activity_id))
    
    # Check if already registered
    cursor.execute('''
        SELECT * FROM registrations
        WHERE activity_id = %s AND volunteer_id = %s
    ''', (activity_id, session['user_id']))
    if cursor.fetchone():
        flash('You have already registered for this activity')
        return redirect(url_for('activity_detail', activity_id=activity_id))
    
    # Create registration record
    cursor.execute('''
        INSERT INTO registrations (activity_id, volunteer_id, status, created_at)
        VALUES (%s, %s, 'pending', NOW())
    ''', (activity_id, session['user_id']))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('Registration successful, pending approval')
    return redirect(url_for('activity_detail', activity_id=activity_id))

@app.route('/organization/activity/<int:activity_id>/edit', methods=['GET', 'POST'])
@login_required
def organization_edit_activity(activity_id):
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Get activity information and ensure it's the organization's activity
    cursor.execute('''
        SELECT * FROM activities 
        WHERE id = %s AND organization_id = %s
        AND status IN ('pending', 'approved')
    ''', (activity_id, session['user_id']))
    activity = cursor.fetchone()
    
    if not activity:
        flash('Activity does not exist or you do not have permission to edit')
        return redirect(url_for('organization_activities'))
    
    if request.method == 'POST':
        title = request.form.get('title')
        description = request.form.get('description')
        category = request.form.get('category')
        location = request.form.get('location')
        start_date = request.form.get('start_date')
        end_date = request.form.get('end_date')
        max_participants = request.form.get('max_participants')
        
        # Validate required fields
        if not all([title, description, category, location, start_date, end_date, max_participants]):
            flash('Please fill in all required fields')
            return redirect(url_for('organization_edit_activity', activity_id=activity_id))
        
        try:
            # Update activity information
            cursor.execute('''
                UPDATE activities 
                SET title = %s, description = %s, category = %s, 
                    location = %s, start_date = %s, end_date = %s, 
                    max_participants = %s, 
                    status = CASE 
                        WHEN status = 'approved' THEN 'approved'
                        ELSE 'pending'
                    END,
                    updated_at = NOW()
                WHERE id = %s AND organization_id = %s
            ''', (
                title, description, category, location,
                start_date, end_date, max_participants,
                activity_id, session['user_id']
            ))
            
            conn.commit()
            flash('Activity updated successfully')
            return redirect(url_for('activity_detail', activity_id=activity_id))
            
        except Exception as e:
            flash('Failed to update activity, please try again later')
            return redirect(url_for('organization_edit_activity', activity_id=activity_id))
        
    cursor.close()
    conn.close()
    
    return render_template('organization/edit_activity.html', activity=activity)

@app.route('/organization/activity/<int:activity_id>/registrations')
@login_required
def organization_registrations(activity_id):
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Verify activity ownership
    cursor.execute('''
        SELECT * FROM activities 
        WHERE id = %s AND organization_id = %s
    ''', (activity_id, session['user_id']))
    activity = cursor.fetchone()
    
    if not activity:
        flash('Activity does not exist or you do not have permission to access')
        return redirect(url_for('organization_activities'))
    
    # Get registration list
    cursor.execute('''
        SELECT r.*, u.name as volunteer_name, u.email as volunteer_email,
               u.phone as volunteer_phone
        FROM registrations r
        JOIN users u ON r.volunteer_id = u.id
        WHERE r.activity_id = %s
        ORDER BY r.created_at DESC
    ''', (activity_id,))
    registrations = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return render_template('organization/registrations.html',
                         activity=activity,
                         registrations=registrations)

@app.route('/organization/registration/<int:registration_id>/approve', methods=['POST'])
@login_required
def organization_approve_registration(registration_id):
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Get registration information and verify activity ownership
    cursor.execute('''
        SELECT r.*, a.organization_id, a.max_participants, a.current_participants
        FROM registrations r
        JOIN activities a ON r.activity_id = a.id
        WHERE r.id = %s
    ''', (registration_id,))
    registration = cursor.fetchone()
    
    if not registration or registration['organization_id'] != session['user_id']:
        flash('Registration record does not exist or you do not have permission')
        return redirect(url_for('organization_activities'))
    
    # Check if there are remaining spots
    if registration['current_participants'] >= registration['max_participants']:
        flash('Activity is full')
        return redirect(url_for('organization_registrations', activity_id=registration['activity_id']))
    
    # Update registration status
    cursor.execute('''
        UPDATE registrations 
        SET status = 'approved', 
            updated_at = NOW()
        WHERE id = %s
    ''', (registration_id,))
    
    # Update activity current participant count
    cursor.execute('''
        UPDATE activities 
        SET current_participants = current_participants + 1
        WHERE id = %s
    ''', (registration['activity_id'],))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('Registration approved')
    return redirect(url_for('organization_registrations', activity_id=registration['activity_id']))

@app.route('/organization/registration/<int:registration_id>/reject', methods=['POST'])
@login_required
def organization_reject_registration(registration_id):
    if session.get('role') != 'organization':
        flash('Access denied')
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    # Get registration information and verify activity ownership
    cursor.execute('''
        SELECT r.*, a.organization_id
        FROM registrations r
        JOIN activities a ON r.activity_id = a.id
        WHERE r.id = %s
    ''', (registration_id,))
    registration = cursor.fetchone()
    
    if not registration or registration['organization_id'] != session['user_id']:
        flash('Registration record does not exist or you do not have permission')
        return redirect(url_for('organization_activities'))
    
    # Update registration status
    cursor.execute('''
        UPDATE registrations 
        SET status = 'rejected',
            reject_reason = %s,
            updated_at = NOW()
        WHERE id = %s
    ''', (request.form.get('reject_reason'), registration_id))
    
    conn.commit()
    cursor.close()
    conn.close()
    
    flash('Registration rejected')
    return redirect(url_for('organization_registrations', activity_id=registration['activity_id'])) 